﻿Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class frm_schedule2

    Dim models As New BindingSource

    Private Sub frm_schedule_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        OpenExcel()

        'select list of model
        GetListModels()

        With data_BDY
            DirectCast(.Columns(0), DataGridViewComboBoxColumn).DataSource = models
        End With
    End Sub

    Private Sub GetListModels()
        models = New BindingSource
        Using connection As New SqlConnection(ConnectionString)
            connection.Open()
            Dim sql = "SELECT DISTINCT ModelID FROM Cycle"

            Dim cmd As New SqlCommand(sql, connection)
            cmd.CommandType = CommandType.Text

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read
                models.Add(reader.GetString(0))
            End While
            reader.Close()
            cmd.Dispose()
        End Using
    End Sub

    Private Sub OpenExcel()
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("C:\Users\avo1\Documents\Visual Studio 2010\Projects\Personal\isuzu\SGcommunication\Isuzu-Host\bin\Debug\test.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets(1)
        xlApp.Visible = True

        Dim shopName As String = xlWorkSheet.Cells(4, 1).value 'NOTE: row first, col later
        Dim day As Integer = xlWorkSheet.Cells(2, 9).value
        Dim month As String = xlWorkSheet.Cells(1, 7).value
        Dim firstRow As Integer = 4
        Dim lastRow As Integer = firstRow
        Dim col As Integer = 9
        Dim val As String
        Dim objRange As Excel.Range = xlWorkSheet.Range("A1", "AZ500")

        'MessageBox.Show(xlWorkSheet.Range("A4").Value2)
        'MessageBox.Show(xlWorkSheet.Range("B4").Value2)
        'MessageBox.Show(xlWorkSheet.Range("C4").Value2)
        'MessageBox.Show(xlWorkSheet.Range("D4").Value2)
        'MessageBox.Show(xlWorkSheet.Range("AC19").Value2)

        MessageBox.Show(objRange.Cells(1, 1).Text)

        'look at col D, until empty then lastRow
        While (True)
            'objRange = xlWorkSheet.Cells(lastRow, 3)
            'If objRange.MergeCells Then
            '    val = objRange.MergeArea(1, 1).Text
            'Else
            '    val = objRange.Text
            'End If

            'val = objRange.Text
            val = xlWorkSheet.Range("D" & lastRow.ToString).Value2
            If val = "" Then
                Exit While
            Else
                lastRow += 1
            End If
            'Console.WriteLine("Cells " & lastRow & ", 4: " & val)
            'MessageBox.Show("Cells " & lastRow & ", 4: " & val)
        End While

        Dim i As Integer
        Dim plan As String = shopName & "|" & month & "-" & day & "|"
        For i = firstRow To lastRow
            If (xlWorkSheet.Cells(i, 2).text <> "") AndAlso (xlWorkSheet.Cells(i, col).text <> "") Then
                plan &= (xlWorkSheet.Cells(i, 2).text & ":" & xlWorkSheet.Cells(i, col).text)
            End If
        Next

        MessageBox.Show(plan)

        'clean up and quit
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet)
        xlWorkSheet = Nothing
        xlWorkBook.Close(False) 'close, no save
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)
        xlWorkBook = Nothing

        xlApp.Workbooks.Close()
        xlApp.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
        xlApp = Nothing
    End Sub
End Class